/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package App;

import java.util.Date;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

/**
 *
 * @author Paul Mallon
 */
public class Database {
    private Connection connect = null;
    private Statement statement = null;
    private PreparedStatement preparedStatement = null;
    private ResultSet resultSet = null;
    private String pin = "1111";
    private String userName = "Paul"; 
    
    public Database(){
    
        connectToDatabase();
       
    }
    

    private void connectToDatabase(){
        
        try{
            Class.forName("com.mysql.jdbc.Driver").newInstance();
            connect = DriverManager.getConnection("jdbc:mysql://localhost:3306/hkrbank?user=hkrbank&password=password");
            statement = connect.createStatement();
            System.out.println("Database connection successful!");
        
        }catch(Exception e){
            System.out.println("Database connection FAILED!!!");
            System.out.println(e);
            e.printStackTrace();
        }
        
        
    }
    
    public boolean confirmUserID(){
        boolean validUser = false;
        try{
            resultSet = statement.executeQuery("SELECT * FROM customer_table");
            
            while(resultSet.next()){
                System.out.println(resultSet.getString("name") + " " + resultSet.getString("pin"));
                if(userName.equals(resultSet.getString("name"))&& 
                        pin.equals(resultSet.getString("pin"))){
                
                    validUser = true;
                }
            }
        
        }catch(Exception e){
        
            System.out.println("SELECT statement FAILED");
        }
        
        return validUser;
        
    }
    public double upDateAccountBalance(double newDeposit){
        double balance = 0;
        try{
    
            resultSet = statement.executeQuery("SELECT * FROM customer_table");
            
            while(resultSet.next()){
                System.out.println(resultSet.getString("name") + " " + resultSet.getString("pin"));
                if(userName.equals(resultSet.getString("name"))&& 
                        pin.equals(resultSet.getString("pin"))){
                    
                    balance = resultSet.getDouble("balance");
                    System.out.println("current balance is: " + balance);
                    balance += newDeposit;
                    
                }
            }
        
        }catch(Exception e){
        
            System.out.println("updateBalance statement FAILED");
            e.printStackTrace();
        }
        
            System.out.println("New balance is: " + balance);
            try{
               
            statement.executeUpdate("UPDATE customer_table SET balance = " + balance + "WHERE pin = " + pin);
            
            }catch(Exception e){
                System.out.println("Database update failed");
                e.printStackTrace();
            }
        return balance;
        
    
    }
    
    public double getBalance(){
        double balance = 0;
        try{
            //resultSet = statement.executeQuery("SELECT 'balance' FROM customer_table WHERE 'name' = " + userName);
            //balance = resultSet.getDouble("balance");
    
            resultSet = statement.executeQuery("SELECT * FROM customer_table");
            
            while(resultSet.next()){
                System.out.println(resultSet.getString("name") + " " + resultSet.getString("pin"));
                if(userName.equals(resultSet.getString("name"))&& 
                        pin.equals(resultSet.getString("pin"))){
                    
                    balance = resultSet.getDouble("balance");
                }
            }
        
        }catch(Exception e){
        
            System.out.println("getBalance statement FAILED");
        }
        System.out.println("The balance is: " + balance);
        return balance;
    }

    /**
     * @return the password
     */
    public String getPassword() {
        return pin;
    }

    /**
     * @param password the password to set
     */
    public void setPassword(String password) {
        this.pin = password;
    }

    /**
     * @return the userName
     */
    public String getUserName() {
        return userName;
    }

    /**
     * @param userName the userName to set
     */
    public void setUserName(String userName) {
        this.userName = userName;
    }
    
    public void setPin(String pin){
        this.pin = pin;
    }
}
